USE [DSRSystem_Temp]
GO

/****** Object:  StoredProcedure [dbo].[spExtractFinalizedIndicators]    Script Date: 1/26/2016 12:54:53 PM ******/
DROP PROCEDURE [dbo].[spExtractFinalizedIndicators]
GO

/****** Object:  StoredProcedure [dbo].[spExtractFinalizedIndicators]    Script Date: 1/26/2016 12:54:53 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[spExtractFinalizedIndicators]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)
	
	
	DECLARE @transaction_isolation_level SMALLINT;

	SELECT @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_requests
    WHERE session_id = @@spid;

    print('spUpdateFinalizedIndicators transaction_isolation_level: ' + STR(@transaction_isolation_level))


	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Indicators') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[Indicators](
			[Id] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[TemplateId] uniqueidentifier NOT NULL,
			[Number] nvarchar(50) NOT NULL,
			[AnswerCategoryCode] nvarchar(max) NOT NULL,
			[AnswerCategoryText] nvarchar(max) NOT NULL,
			[AnswerCategoryScore] int NOT NULL,
		) ON [PRIMARY]
	END
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.StageIndicators') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[StageIndicators](
			[Id] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[TemplateId] uniqueidentifier NOT NULL,
			[Number] nvarchar(50) NOT NULL,
			[AnswerCategoryCode] nvarchar(max) NOT NULL,
			[AnswerCategoryText] nvarchar(max) NOT NULL,
			[AnswerCategoryScore] int NOT NULL,
		) ON [PRIMARY]
	END
	
	declare @startTime datetime
	declare @endTime datetime
	declare @elapsedTime int
	declare @rowsAffected int
	declare @Log table
	(
		Description nvarchar(max) NOT NULL,
		ElapsedTime int NOT NULL,
		RowsAffected int NOT NULL
	)

	select @startTime = getdate()

	TRUNCATE TABLE [dbo].[StageIndicators]
	INSERT INTO [dbo].[StageIndicators]
	EXECUTE [DSRSystem].[dbo].[spGetStandardIndicatorByCompanyIds] @CompanyIds

	set @rowsAffected = @@ROWCOUNT
	select @endTime = getdate()
	select @elapsedTime = datediff(second, @startTime, @endTime)
--	insert into @Log (Description, ElapsedTime, RowsAffected)
--	values ('Extract Finalized Indicators', @elapsedTime, @rowsAffected)

	--SELECT * FROM @Log
END

GO


